package com.huatai.datacenter.utils;

import com.alibaba.cloud.commons.lang.StringUtils;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.fastjson.JSONObject;
import com.huatai.common.api.R;
import com.huatai.datacenter.config.PoolManager;
import com.huatai.datacenter.entity.DataSourceEntity;
import com.huatai.datacenter.entity.datamodel.DataModelEntity;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * @author Lion
 * @date 2023/3/13  11:43
 */
@Slf4j
public class JDBCUtil {

	private static JdbcTemplate jdbcTemplate;

	private static String driver;

	private static String url;

	private static String userName;

	private static String passWord;

	/**
	 * @author Lion
	 * @description 根据数据库连接参数进行连接
	 * @param ds
	 * @return java.sql.Connection
	 */
	public static Connection getConnection(DataSourceEntity ds) throws SQLException {

		try {
			Class.forName(ds.getDriver());
			Connection connection = DriverManager.getConnection(ds.getUrl(),ds.getUsername(),CryptoUtil.decode(ds.getPassword()));
			return connection;
		} catch (ClassNotFoundException e) {
			throw new RuntimeException("Please check whether the jdbc driver jar is missing, if missed copy the jdbc jar file to lib dir. "+ e.getMessage());
		}
	}

	/**
	 * @author Lion
	 * @description 根据数据库连接参数进行连接
	 * @param ds
	 * @return java.sql.Connection
	 */
	public static Connection getConnectionDes(DataSourceEntity ds) throws SQLException {
		try {
		//判断是否携带id

			Class.forName(ds.getDriver());
			Connection connection = DriverManager.getConnection(ds.getUrl(),ds.getUsername(),ds.getPassword());
			return connection;
		} catch (ClassNotFoundException e) {
			throw new RuntimeException("Please check whether the jdbc driver jar is missing, if missed copy the jdbc jar file to lib dir. "+ e.getMessage());
		}
	}

	/**
	 * @author Lion
	 * @description 根据连接信息和SQL返回所有表名
	 * @param connection
	 * @param SQL
	 * @return java.util.List<java.lang.String>
	 */
	public static List<String> getAllTables(Connection connection, String SQL){

		List<String> list = new ArrayList<>();

		PreparedStatement pst = null;

		try {
			pst = connection.prepareStatement(SQL);
			ResultSet resultSet = pst.executeQuery();

			while (resultSet.next()){
				String s = resultSet.getString(1);
				list.add(s);
			}
			return list;

		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}finally {
			try {
			if (pst != null){
					pst.close();
				}
			if (connection != null){
				connection.close();
			}
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
	}

	/**
	 * @author Lion
	 * @description 获取所有表的数据量
	 * @param connection
	 * @param SQL
	 * @return java.util.List<java.lang.String>
	 */
	public static List<JSONObject> getAllTableCount(Connection connection, String SQL){

		List<JSONObject> list = new ArrayList<>();
		JSONObject jsonObject1 = new JSONObject();
		JSONObject jsonObject2 = new JSONObject();
		int dataCount = 0;
		int dataCount2 = 0;

		PreparedStatement pst = null;

		try {
			pst = connection.prepareStatement(SQL);
			ResultSet resultSet = pst.executeQuery();

			while (resultSet.next()){
				String s = resultSet.getString(1);
				if (s.contains("tb_rt_")){
					int count =  resultSet.getRow();
					dataCount = dataCount + count;
				}else {
					int count2 = resultSet.getRow();
					dataCount2 = dataCount2 + count2;
				}
			}

			jsonObject1.put("实时数据量",dataCount);
			jsonObject2.put("http请求数据量",dataCount2);

			list.add(jsonObject1);
			list.add(jsonObject2);

			return list;

		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}finally {
			try {
				if (pst != null){
					pst.close();
				}
				if (connection != null){
					connection.close();
				}
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
	}

	/**
	 * @author Lion
	 * @description 执行SQL获取数据
	 * @param dataSource 数据源连接
	 * @param sql 语句
	 * @param jdbcParamValues 参数
	 * @return com.huatai.common.api.R
	 */
	public static R executeSql(DataSourceEntity dataSource, String sql, List<Object> jdbcParamValues) throws SQLException {

		Connection connection = null;
		try{
			connection = JDBCUtil.getConnection(dataSource);
			PreparedStatement statement = connection.prepareStatement(sql);
			//参数注入
			for (int i=1;i <= jdbcParamValues.size(); i++){
				statement.setObject(i,jdbcParamValues.get(i-1));
			}
			//校验参数
			boolean hasResultSet = statement.execute();
			if (hasResultSet){
				ResultSet rs = statement.getResultSet();
				int columnCount = rs.getMetaData().getColumnCount();

				List<String> columns = new ArrayList<>();
				for (int i = 1;i<= columnCount; i++){
					String columnName = rs.getMetaData().getColumnLabel(i);
					columns.add(columnName);
				}
				List<JSONObject> list = new ArrayList<>();
				while (rs.next()){
					JSONObject JSON = new JSONObject();
					columns.stream().forEach(t -> {
						try {
							Object value = rs.getObject(t);
							JSON.put(t,value);
						} catch (SQLException throwables) {
							throwables.printStackTrace();
						}
					});
					list.add(JSON);
				}
				return R.data(list);
			}else {
				int updateCount = statement.getUpdateCount();
				return R.data(updateCount + " rows affected");
			}
		}catch (Exception e){
			e.printStackTrace();
			return R.data(e.getMessage());
		}finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
	}

	/**
	 * @author Lion
	 * @description 查询表中所有字段
	 * @param connection
	 * @param type
	 * @param table
	 * @return java.util.List<com.alibaba.fastjson.JSONObject>
	 */
	public static List<JSONObject> getRDBMSColumnProperties(Connection connection,String type,String table){
		List<JSONObject> list = new ArrayList<>();

		try {
		String sql;
		switch (type){
			case "POSTGRESQL":
				sql = "select * from \" "+ table + "\" where 1=2";
				break;
			default:
				sql = "select * from " + table ;
		}
		log.info(sql);
		Statement statement = connection.createStatement();

		ResultSet result = statement.executeQuery(sql);
		ResultSetMetaData rsd = result.getMetaData();

		for (int i=0;i< rsd.getColumnCount();i++){
			JSONObject jsonObject = new JSONObject();

			String columnTypeName = rsd.getColumnTypeName(i+1);
			jsonObject.put("fieldTypeName",columnTypeName);
			jsonObject.put("typeName",columnTypeName);
			jsonObject.put("fieldJavaTypeName",rsd.getColumnClassName(i+1));//映射到java的类型名
			String columnName =rsd.getColumnName(i + 1);
			if (columnName.contains(".")){
				columnName = columnName.split("\\.")[1];
			}
			jsonObject.put("label",columnName); //表字段
			list.add(jsonObject);
		}
		return list;

		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}


	/**
	 * @author Lion
	 * @description 获取jdbc信息
	 * @param  sql
	 * @param params
	 * @return java.util.Map<java.lang.String,java.lang.Object>
	 */
	public Map<String, Object> queryForMap(String sql, Object... params)
	{
		return jdbcTemplate.queryForMap(sql, params);
	}

	/**
	 * @author Lion
	 * @description jdbc连接
	 * @param  dataSource
	 * @param tableName
	 * @return
	 */
	public JDBCUtil(DataSourceEntity dataSource,String tableName)
	{

		String databaseType = dataSource.getDatabaseType();
		switch (databaseType)
		{
			case "MySql":
				driver = "com.mysql.cj.jdbc.Driver";
				url = "jdbc:mysql://" + dataSource.getUrl() + "?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&autoReconnect=true&allowMultiQueries=true&rewriteBatchedStatements=true&useSSL=false";
				break;
			/*case "MariaDB":
				driver = "com.mysql.jdbc.Driver";
				url = "jdbc:mysql://" + ip + ":" + port + "/" + dbName + "?characterEncoding=utf8&tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true";
				break;
			case "Oracle":
				driver = "oracle.jdbc.driver.OracleDriver";
				url = "jdbc:oracle:thin:@" + ip + ":" + port + ":" + dbName;
				break;
			case "PostgreSQL":
				driver = "org.postgresql.Driver";
				url = "jdbc:postgresql://" + ip + ":" + port + "/" + dbName;
				break;
			case "MSSQL":
				driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
				url = "jdbc:sqlserver://" + ip + ":" + port + ";database=" + dbName;
				break;
			case "Hive2":
				driver = "org.apache.hive.jdbc.HiveDriver";
				url = "jdbc:hive2://" + ip + ":" + port + "/" + dbName;
				break;
			default:
				break;*/
		}
		userName = dataSource.getUsername();
		passWord = CryptoUtil.decode(dataSource.getPassword());

	}

	/**
	 * @author Lion
	 * @description 执行SQL语句
	 * @param sql
	 * @param conn
	 * @return int
	 */
	public static int setupdateData(String sql,Connection conn )
		throws Exception
	{
		Statement stmt = null;
		try
		{
			stmt = conn.createStatement();
			return stmt.executeUpdate(sql);
		}
		catch (SQLException e)
		{
			log.error(e.getMessage(), e);
			throw new Exception(e.getMessage());
		}
		finally
		{
			try
			{
				stmt.close();
				conn.close();
			}
			catch (SQLException e)
			{
				log.error(e.getMessage(), e);
			}
		}
	}


	/**
	 * @author Lion
	 * @description 根据表名和模型拼接sql语句
	 * @param url
	 * @param userName
	 * @param password
	 * @param tableName
	 * @param dataModelEntities
	 * @return void
	 */
	public void createTable(String url, String userName, String password, String tableName, List<DataModelEntity> dataModelEntities) throws ClassNotFoundException, SQLException {

		//需要修改

		//连接数据库
		Class.forName(driver);
		Connection connection = DriverManager.getConnection(url, userName, password);

		Statement stat = connection.createStatement();
		//获取数据库表名
		ResultSet resultSet = connection.getMetaData().getTables(null, null, "sys_admin_divisions", null);

		if (resultSet.next()) {
			return;
		} else {
			String sql = "CREATE TABLE " + tableName + "(id bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=" + "\'"  + "\'" + ";";
			stat.executeUpdate(sql);
		}
		//关闭连接
		stat.close();
		connection.close();
	}


	/*public String createTable(String objectTableName, List<DataModelEntity> dataModelEntities,String pk) throws SQLException {

		StringBuilder start = new StringBuilder().append("CREATE TABLE ")
			.append(objectTableName).append("(");
		StringBuilder builder = new StringBuilder();
		Iterator<DataModelEntity> iterable = dataModelEntities.iterator();
		while (iterable.hasNext()) {

			String field_name = columnResultSet.getString("COLUMN_NAME");
			String field_type = columnResultSet.getString("TYPE_NAME");
			int field_length = columnResultSet.getInt("COLUMN_SIZE");

			int field_is_null = columnResultSet.getInt("NULLABLE");
			String remarks = columnResultSet.getString("REMARKS");
			builder.append("`");
			builder.append(field_name).append("`").append(" ");
			//如果为时间类型  长度为3
			if ("DATETIME".equals(field_type)){
				builder.append(field_type).append("(").append(3).append(")").append(" ");
			}else {
				builder.append(field_type).append("(").append(field_length).append(")").append(" ");
			}
			if (field_is_null == 1) {
				builder.append(" DEFAULT NULL ");
			} else {
				builder.append(" NOT NULL ");
			}
			builder.append("COMMENT").append(" ").append("'").append(remarks).append("'");

			builder.append(",");

		}
		builder.append("PRIMARY KEY (`").append(pk).append("`) USING BTREE");
		StringBuilder end = new StringBuilder().append(")").append("ENGINE=InnoDB DEFAULT CHARSET=utf8").append(" ")
			.append("COMMENT=").append("'").append("").append("'");
		StringBuilder sqlString = start.append(builder).append(end);
		return sqlString.toString();
	}*/


}
